from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from db_connection import Database
from llm_service import LLMService
import uvicorn
import logging
from datetime import datetime
import socket

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('app.log'),
        logging.StreamHandler()
    ]
)

def is_port_in_use(port):
    """检查端口是否被占用"""
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
        try:
            s.bind(('0.0.0.0', port))
            return False
        except socket.error:
            return True

def find_available_port(start_port=5000, max_port=5100):
    """查找可用的端口"""
    for port in range(start_port, max_port + 1):
        if not is_port_in_use(port):
            return port
    raise RuntimeError(f"在端口 {start_port}-{max_port} 范围内没有找到可用端口")

app = FastAPI(
    title="MySQL Natural Language Query Service",
    description="一个基于自然语言的MySQL数据库查询服务",
    version="1.0.0"
)

class QueryRequest(BaseModel):
    query: str

@app.post("/query")
async def process_query(request: QueryRequest):
    try:
        # 记录请求
        logging.info(f"收到查询请求: {request.query}")
        
        # 初始化服务
        llm_service = LLMService()
        db = Database()
        
        # 生成SQL
        sql_queries = llm_service.generate_sql(request.query)
        logging.info(f"生成的SQL: {sql_queries}")
        
        # 执行所有SQL语句
        results = []
        for sql_query in sql_queries:
            try:
                # 如果是表结构查询，使用DESCRIBE命令
                if "describe" in sql_query.lower() or "show columns" in sql_query.lower():
                    result = db.execute_query(sql_query)
                    # 将结果转换为更易读的格式
                    formatted_result = []
                    for row in result:
                        formatted_result.append({
                            "字段名": row["Field"],
                            "类型": row["Type"],
                            "是否为空": row["Null"],
                            "键": row["Key"],
                            "默认值": row["Default"],
                            "额外信息": row["Extra"]
                        })
                    results.append({
                        "sql_query": sql_query,
                        "result": formatted_result
                    })
                else:
                    result = db.execute_query(sql_query)
                    results.append({
                        "sql_query": sql_query,
                        "result": result
                    })
            except Exception as e:
                logging.error(f"执行SQL时发生错误: {str(e)}")
                results.append({
                    "sql_query": sql_query,
                    "result": f"Error: {str(e)}"
                })
        
        # 关闭数据库连接
        db.close()
        
        return {
            "queries": results
        }
    except Exception as e:
        error_msg = f"处理查询时发生错误: {str(e)}"
        logging.error(error_msg)
        raise HTTPException(status_code=500, detail=error_msg)

@app.get("/health")
async def health_check():
    """健康检查接口"""
    return {
        "status": "healthy",
        "timestamp": datetime.now().isoformat()
    }

if __name__ == "__main__":
    try:
        port = find_available_port()
        logging.info(f"使用端口: {port}")
        uvicorn.run(app, host="0.0.0.0", port=port)
    except Exception as e:
        logging.error(f"启动服务失败: {str(e)}") 